﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using System.Text;
using Lib.Common;
using Microsoft.ApplicationBlocks.Data;
using Module.Core.EntitiesInfo;
using Module.EntitiesInfo;

namespace Module.DataAccess
{
   public class AdvertisementDB
    {
        #region ConnectionString
        private string _connectionString;
        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        public AdvertisementDB()
        {
            ConnectionString = ConfigurationManager.ConnectionStrings["localsql"].ToString();
        }
        #endregion
        #region Methods
        public virtual int Insert(AdvertisementInfo advertisementInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Title", advertisementInfo.Title),
				new SqlParameter("@Description", advertisementInfo.Description),
				new SqlParameter("@MediaSrc", advertisementInfo.MediaSrc),
				new SqlParameter("@Width", advertisementInfo.Width),
				new SqlParameter("@Height", advertisementInfo.Height),
				new SqlParameter("@MediaType", advertisementInfo.MediaType),
				new SqlParameter("@Target", advertisementInfo.Target),
				new SqlParameter("@Priority", advertisementInfo.Priority),
				new SqlParameter("@CreatedBy", advertisementInfo.CreatedBy),
				new SqlParameter("@CreatedOn", advertisementInfo.CreatedOn),
				new SqlParameter("@StartDate", advertisementInfo.StartDate),
				new SqlParameter("@EndDate", advertisementInfo.EndDate),
				new SqlParameter("@CustomerID", advertisementInfo.CustomerID),
				new SqlParameter("@Click", advertisementInfo.Click),
				new SqlParameter("@ViewCount", advertisementInfo.ViewCount),
				new SqlParameter("@IsEnable", advertisementInfo.IsEnable),
				new SqlParameter("@PositionID", advertisementInfo.PositionID)

			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Advertisement_Insert", parameters);
        }

        public virtual void Update(AdvertisementInfo advertisementInfo)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@Title", advertisementInfo.Title),
				new SqlParameter("@Description", advertisementInfo.Description),
				new SqlParameter("@MediaSrc", advertisementInfo.MediaSrc),
				new SqlParameter("@Width", advertisementInfo.Width),
				new SqlParameter("@Height", advertisementInfo.Height),
				new SqlParameter("@MediaType", advertisementInfo.MediaType),
				new SqlParameter("@Target", advertisementInfo.Target),
				new SqlParameter("@Priority", advertisementInfo.Priority),
				new SqlParameter("@CreatedBy", advertisementInfo.CreatedBy),
				new SqlParameter("@CreatedOn", advertisementInfo.CreatedOn),
				new SqlParameter("@StartDate", advertisementInfo.StartDate),
				new SqlParameter("@EndDate", advertisementInfo.EndDate),
				new SqlParameter("@CustomerID", advertisementInfo.CustomerID),
				new SqlParameter("@Click", advertisementInfo.Click),
				new SqlParameter("@ViewCount", advertisementInfo.ViewCount),
				new SqlParameter("@IsEnable", advertisementInfo.IsEnable),
				new SqlParameter("@PositionID", advertisementInfo.PositionID)
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Advertisement_Update", parameters);
        }

        /// <summary>
        /// Deletes a record from the Advertisement table by its primary key.
        /// </summary>
        public virtual void Delete(int advertisementId)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@AdvertisementID", advertisementId)
                            
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Advertisement_Delete", parameters);
        }

        public virtual DataTable GetAll()
        {
            DataTable dt = SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Advertisement_GetAll").Tables[0];
            return dt;

        }
        public IDataReader Select(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Advertisement_Select", type, para_1, para_2);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Advertisement_Select", type, para_1);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public IDataReader Select(int? type)
        {
            try
            {
                return SqlHelper.ExecuteReader(ConnectionString, "Advertisement_Select", type);
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1, string para_2)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Advertisement_Select", type, para_1, para_2).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type, string para_1)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Advertisement_Select", type, para_1).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }
        }
        public DataTable SelectTbl(int? type)
        {
            try
            {
                return SqlHelper.ExecuteDataset(ConnectionString, "Advertisement_Select", type).Tables[0];
            }
            catch (Exception)
            {

                throw;
            }

        }
        /// <summary>
        /// Selects all records from the Advertisement table.
        /// </summary>
        public CHRTList<AdvertisementInfo> SelectAll()
        {
            using (SqlDataReader dataReader = SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Advertisement_SelectAll"))
            {
                CHRTList<AdvertisementInfo> advertisementInfoList = new CHRTList<AdvertisementInfo>();
                while (dataReader.Read())
                {
                    AdvertisementInfo advertisementInfo = new AdvertisementInfo();
                    advertisementInfoList.Add(advertisementInfo);
                }

                return advertisementInfoList;
            }
        }




        public virtual int GetCount(int positionID,int isEnable, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                          
               new SqlParameter("@PositionID", positionID),  
                 new SqlParameter("@IsEnable", isEnable), 
               new SqlParameter("@starDatetime", starDatetime),
               new SqlParameter("@endDatetime", endDatetime),
               new SqlParameter("@IsTotalRow", 1)
			};

            return (int)SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Advertisement_Search", parameters);
        }

       
        public virtual DataTable Search(int positionID,int isEnable, DateTime starDatetime, DateTime endDatetime, int beginRow, int endRow, string orderByColumn)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{                
                
                new SqlParameter("@PositionID", positionID),  
                new SqlParameter("@IsEnable", isEnable), 
                new SqlParameter("@StarDatetime", starDatetime),
                new SqlParameter("@EndDatetime", endDatetime),               
                new SqlParameter("@BeginRow", beginRow),
                new SqlParameter("@EndRow", endRow),
                new SqlParameter("@OrderByColumn", orderByColumn),
				new SqlParameter("@IsTotalRow", 0)
			};

            return SqlHelper.ExecuteDataset(ConnectionString, CommandType.StoredProcedure, "Advertisement_Search", parameters).Tables[0];
        }
        public virtual void UpdateStatus(int advertisementID, int isEnable, int param)
        {
            SqlParameter[] parameters = new SqlParameter[]
			{
				new SqlParameter("@AdvertisementID", advertisementID),
                new SqlParameter("@IsEnable", isEnable),
                new SqlParameter("@Param", param),
			};

            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Advertisement_UpdateStatus", parameters);
        }

      #endregion
    }
}
